﻿using System;

using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using TimeTableTool.DTO;

namespace TimeTableTool
{
    class TimeTableDao 
    {
        SQLiteConnection connection;

        public TimeTableDao(SQLiteConnection connection)
        {
            this.connection = connection;
        }

        public TimeTableDTO GetTimeTable(int timeTableId)
        {
            SQLiteCommand command = new SQLiteCommand(connection);
            command.CommandText = "SELECT * FROM time_table WHERE id=@idValue";
            command.Parameters.AddWithValue("idValue", timeTableId);

            List<TimeTableDTO> timeTableList = GetTimeTables(command);
            if (timeTableList.Count == 1)
            {
                return timeTableList[0];
            }
            else
            {
                if (timeTableList.Count < 1)
                {
                    return null;
                }
                else
                {
                    throw new ArgumentException("Something went terribly wrong while searching timetables");
                }
            }

        }

        public List<TimeTableDTO> GetTimeTables(SQLiteCommand cmnd)
        {
            using (cmnd)
            {
                using (SQLiteDataReader reader = cmnd.ExecuteReader())
                {
                    List<TimeTableDTO> timeTableList = new List<TimeTableDTO>();
                    while (reader.Read())
                    {
                        TimeTableDTO timeTable = new TimeTableDTO();
                        timeTable.Id = int.Parse(reader["id"].ToString());
                        timeTable.LineId = int.Parse(reader["line_id"].ToString());
                        timeTable.StationId = int.Parse(reader["station_id"].ToString());
                        timeTable.DayType = reader["day_type"].ToString();
                        timeTable.Time = int.Parse(reader["time"].ToString());
                        timeTableList.Add(timeTable);
                    }
                    return timeTableList;
                }
            }
        }

        public void BulkInsert(List<TimeTableDTO> timetables)
        {
            SQLiteCommand insertCmnd = new SQLiteCommand(connection);
            insertCmnd.CommandText = "INSERT INTO time_table (line_id, station_id, time, day_type) "+
                                     "VALUES (@lineIdValue, @stationId,@timeValue, @dayTypeValue);";

            SQLiteTransaction transaction = connection.BeginTransaction();
            foreach (TimeTableDTO timeTable in timetables)
            {
                insertCmnd.Parameters.AddWithValue("lineIdValue", timeTable.LineId);
                insertCmnd.Parameters.AddWithValue("stationId", timeTable.StationId);
                insertCmnd.Parameters.AddWithValue("timeValue", timeTable.Time);
                insertCmnd.Parameters.AddWithValue("dayTypeValue", timeTable.DayType);
                insertCmnd.ExecuteNonQuery();
            }
            transaction.Commit();

           
        }

    }
}
